
// =================================================================== 
// class description : 
// =================================================================== 
// File: ViolationNoticeDBHelper.cs
// Copyright  @ AC
// History:
// 		2012/7/25  created by 
// 
// =================================================================== 

using System;
using System.Text;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

using InternalManagementSystem.DAO.Models;

namespace InternalManagementSystem.DAO
{
    public class ViolationNoticeDAO : DataAccessObject
    {
        /// <summary>
        ///Description   :gets a ViolationNotice record from db
        ///				  package , should be implicly downcast
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : ViolationNotice object. Will be null if record not found.
        /// </summary>
        public ViolationNotice LoadViolationNotice(System.Int32 id)
        {
            string sqlSelect = "SELECT  ID,  UserID,  CompanyID,  NoticeNo,  ViolationDate,  Address,  Details,Committer,CommitDate  FROM ViolationNotice WHERE  ID = @ID ";
            SqlConnection conn = new SqlConnection(ConnectionString);
            ViolationNotice pViolationNotice = null;

            //SqlCommand sqlCmd = new SqlCommand("usSelectViolationNotice");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            sqlCmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
            try
            {
                // open the connection and create the DataReader
                conn.Open();
                SqlDataReader dr = sqlCmd.ExecuteReader();

                // output the results and close the connection. Should be <=1 results.
                if (dr.Read())
                {
                    pViolationNotice = new ViolationNotice();
                    pViolationNotice.ID = (dr["ID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["ID"]);
                    pViolationNotice.UserID = (dr["UserID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["UserID"]);
                    pViolationNotice.CompanyID = (dr["CompanyID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CompanyID"]);
                    pViolationNotice.NoticeNo = (dr["NoticeNo"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["NoticeNo"]);
                    pViolationNotice.ViolationDate = (dr["ViolationDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dr["ViolationDate"]);
                    pViolationNotice.Address = (dr["Address"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Address"]);
                    pViolationNotice.Details = (dr["Details"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Details"]);
                    pViolationNotice.Committer = (dr["Committer"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["Committer"]);
                    pViolationNotice.CommitDate = (dr["CommitDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dr["CommitDate"]);
                }

                conn.Close();

            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return pViolationNotice;
        }

        /// <summary>
        ///Description   :gets a ViolationNotice collection object of  all
        ///				  ViolationNotice records in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : ViolationNotice collection object.
        /// </summary>
        public ViolationNoticeCollection LoadAllViolationNotices()
        {
            string sqlSelect = "SELECT  ID,  UserID,  CompanyID,  NoticeNo,  ViolationDate,  Address,  Details,Committer,CommitDate  FROM ViolationNotice";
            SqlConnection conn = new SqlConnection(ConnectionString);
            ViolationNoticeCollection pViolationNoticeCollection = new ViolationNoticeCollection();

            //SqlCommand sqlCmd = new SqlCommand("usSelectViolationNoticeAll");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                // open the connection and create the DataReader
                conn.Open();
                SqlDataReader dr = sqlCmd.ExecuteReader();

                // output the results and add to collection and close the connection.
                while (dr.Read())
                {
                    ViolationNotice vo = new ViolationNotice();
                    vo.ID = (dr["ID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["ID"]);
                    vo.UserID = (dr["UserID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["UserID"]);
                    vo.CompanyID = (dr["CompanyID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CompanyID"]);
                    vo.NoticeNo = (dr["NoticeNo"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["NoticeNo"]);
                    vo.ViolationDate = (dr["ViolationDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dr["ViolationDate"]);
                    vo.Address = (dr["Address"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Address"]);
                    vo.Details = (dr["Details"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Details"]);
                    vo.Committer = (dr["Committer"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["Committer"]);
                    vo.CommitDate = (dr["CommitDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dr["CommitDate"]);

                    pViolationNoticeCollection.Add(vo);
                }

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return pViolationNoticeCollection;
        }

        /// <summary>
        ///Description   :gets a ViolationNotice collection object of  all
        ///				  ViolationNotice records in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : ViolationNotice collection object.
        /// </summary>
        public ViolationNoticeCollection LoadAllViolationNotices(string filter, string sort)
        {
            string sqlSelect = "SELECT  ID,  UserID,  CompanyID,  NoticeNo,  ViolationDate,  Address,  Details,Committer,CommitDate  FROM ViolationNotice ";
            if (!string.IsNullOrEmpty(filter))
            {
                sqlSelect += " WHERE " + filter;
            }
            if (!string.IsNullOrEmpty(sort))
            {
                sqlSelect += " order by " + sort;
            }
            SqlConnection conn = new SqlConnection(ConnectionString);
            ViolationNoticeCollection pViolationNoticeCollection = new ViolationNoticeCollection();

            //SqlCommand sqlCmd = new SqlCommand("usSelectViolationNoticeDynamic");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            //sqlCmd.Parameters.Add("@WhereCondition",SqlDbType.NVarChar).Value = filter;
            //sqlCmd.Parameters.Add("@OrderByExpression",SqlDbType.NVarChar).Value = sort;

            try
            {
                // open the connection and create the DataReader
                conn.Open();
                SqlDataReader dr = sqlCmd.ExecuteReader();

                // output the results and add to collection and close the connection.
                while (dr.Read())
                {
                    ViolationNotice vo = new ViolationNotice();
                    vo.ID = (dr["ID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["ID"]);
                    vo.UserID = (dr["UserID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["UserID"]);
                    vo.CompanyID = (dr["CompanyID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CompanyID"]);
                    vo.NoticeNo = (dr["NoticeNo"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["NoticeNo"]);
                    vo.ViolationDate = (dr["ViolationDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dr["ViolationDate"]);
                    vo.Address = (dr["Address"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Address"]);
                    vo.Details = (dr["Details"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Details"]);
                    vo.Committer = (dr["Committer"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["Committer"]);
                    vo.CommitDate = (dr["CommitDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dr["CommitDate"]);

                    pViolationNoticeCollection.Add(vo);
                }

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return pViolationNoticeCollection;
        }



        /// <summary>
        ///Description   :gets a DataSet collection object of  all
        ///				  ViolationNotice records in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : DataSet object.
        /// </summary>
        public DataSet GetDataSet()
        {
            DataSet ds = null;
            SqlDataAdapter xAdapter = null;

            string sqlSelect = "SELECT  ID,  UserID,  CompanyID,  NoticeNo,  ViolationDate,  Address,  Details ,Committer,CommitDate FROM ViolationNotice";
            SqlConnection conn = new SqlConnection(ConnectionString);

            //SqlCommand sqlCmd = new SqlCommand("usSelectViolationNoticeAll");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                conn.Open();

                xAdapter = new SqlDataAdapter();
                xAdapter.SelectCommand = sqlCmd;
                ds = new DataSet();
                xAdapter.Fill(ds, "Table");
                xAdapter.Dispose();
                sqlCmd.Dispose();

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return ds;
        }

        /// <summary>
        ///Description   :gets a DataSet collection object of  all
        ///				  ViolationNotice records in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : DataSet object.
        /// </summary>
        public DataSet GetDataSet(string filter, string sort)
        {
            DataSet ds = null;
            SqlDataAdapter xAdapter = null;

            string sqlSelect = "SELECT  ID,  UserID,  CompanyID,  NoticeNo,  ViolationDate,  Address,  Details,Committer,CommitDate  FROM ViolationNotice ";
            if (!string.IsNullOrEmpty(filter))
            {
                sqlSelect += " WHERE " + filter;
            }
            if (!string.IsNullOrEmpty(sort))
            {
                sqlSelect += " order by " + sort;
            }
            SqlConnection conn = new SqlConnection(ConnectionString);

            //SqlCommand sqlCmd = new SqlCommand("usSelectViolationNoticeDynamic");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            //sqlCmd.Parameters.Add("@WhereCondition",SqlDbType.NVarChar).Value = filter;
            //sqlCmd.Parameters.Add("@OrderByExpression",SqlDbType.NVarChar).Value = sort;

            try
            {
                conn.Open();

                xAdapter = new SqlDataAdapter();
                xAdapter.SelectCommand = sqlCmd;
                ds = new DataSet();
                xAdapter.Fill(ds, "Table");
                xAdapter.Dispose();
                sqlCmd.Dispose();

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return ds;
        }

        

        /// <summary>
        ///Description   :updates a ViolationNotice record in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : true - if record was update .
        //				   false - if record was not updated;
        /// </summary>
        public void UpdateViolationNotice(ViolationNotice pViolationNotice)
        {
            string sqlUpdate = "UPDATE ViolationNotice SET  UserID = @UserID, CompanyID = @CompanyID, NoticeNo = @NoticeNo, ViolationDate = @ViolationDate, Address = @Address, Details = @Details,Committer = @Committer,CommitDate = @CommitDate WHERE ID = @ID  ";
            SqlConnection conn = new SqlConnection(ConnectionString);

            // create the command for the stored procedure
            //SqlCommand sqlCmd = new SqlCommand("usInsertUpdateViolationNotice");
            SqlCommand sqlCmd = new SqlCommand(sqlUpdate);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            sqlCmd.Parameters.Add("@ID", SqlDbType.Int).Value = pViolationNotice.ID;
            sqlCmd.Parameters.Add("@UserID", SqlDbType.Int).Value = pViolationNotice.UserID;
            sqlCmd.Parameters.Add("@CompanyID", SqlDbType.Int).Value = pViolationNotice.CompanyID;
            sqlCmd.Parameters.Add("@Committer", SqlDbType.Int).Value = pViolationNotice.Committer;
            sqlCmd.Parameters.Add("@NoticeNo", SqlDbType.VarChar).Value = pViolationNotice.NoticeNo;
            if (pViolationNotice.ViolationDate == DateTime.MinValue)
            {
                sqlCmd.Parameters.Add("@ViolationDate", SqlDbType.DateTime).Value = System.Data.SqlTypes.SqlDateTime.MinValue;
            }
            else
            {
                sqlCmd.Parameters.Add("@ViolationDate", SqlDbType.DateTime).Value = pViolationNotice.ViolationDate;
            }

            if (pViolationNotice.CommitDate == DateTime.MinValue)
            {
                sqlCmd.Parameters.Add("@CommitDate", SqlDbType.DateTime).Value = System.Data.SqlTypes.SqlDateTime.MinValue;
            }
            else
            {
                sqlCmd.Parameters.Add("@CommitDate", SqlDbType.DateTime).Value = pViolationNotice.CommitDate;
            }

            sqlCmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = pViolationNotice.Address;
            sqlCmd.Parameters.Add("@Details", SqlDbType.VarChar).Value = pViolationNotice.Details;



            try
            {
                // open the connection
                conn.Open();

                //execute the update command
                sqlCmd.ExecuteNonQuery();

                //close the connection
                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }
        }

        /// <summary>
        ///Description   : create a new ViolationNotice record in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : None.
        /// </summary>
        public void InsertViolationNotice(ViolationNotice pViolationNotice)
        {
            string sqlInsert = " INSERT INTO ViolationNotice (UserID,CompanyID,NoticeNo,ViolationDate,Address,Details,Committer,CommitDate) VALUES (  @UserID, @CompanyID, @NoticeNo, @ViolationDate, @Address, @Details,@Committer,@CommitDate) ";
            SqlConnection conn = new SqlConnection(ConnectionString);

            // create the command for the stored procedure
            //SqlCommand sqlCmd = new SqlCommand("usInsertViolationNotice");
            SqlCommand sqlCmd = new SqlCommand(sqlInsert);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;


            sqlCmd.Parameters.Add("@UserID", SqlDbType.Int).Value = pViolationNotice.UserID;
            sqlCmd.Parameters.Add("@CompanyID", SqlDbType.Int).Value = pViolationNotice.CompanyID;
            sqlCmd.Parameters.Add("@NoticeNo", SqlDbType.VarChar).Value = pViolationNotice.NoticeNo;
            sqlCmd.Parameters.Add("@Committer", SqlDbType.Int).Value = pViolationNotice.Committer;
            if (pViolationNotice.ViolationDate == DateTime.MinValue)
            {
                sqlCmd.Parameters.Add("@ViolationDate", SqlDbType.DateTime).Value = System.Data.SqlTypes.SqlDateTime.MinValue;
            }
            else
            {
                sqlCmd.Parameters.Add("@ViolationDate", SqlDbType.DateTime).Value = pViolationNotice.ViolationDate;
            }

            if (pViolationNotice.CommitDate == DateTime.MinValue)
            {
                sqlCmd.Parameters.Add("@CommitDate", SqlDbType.DateTime).Value = System.Data.SqlTypes.SqlDateTime.MinValue;
            }
            else
            {
                sqlCmd.Parameters.Add("@CommitDate", SqlDbType.DateTime).Value = pViolationNotice.CommitDate;
            }


            sqlCmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = pViolationNotice.Address;
            sqlCmd.Parameters.Add("@Details", SqlDbType.VarChar).Value = pViolationNotice.Details;

            try
            {
                // open the connection and run the insert command
                conn.Open();
                sqlCmd.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }
        }

        /// <summary>
        ///Description   : Deletes a ViolationNotice record from the db
        ///Pre-condition : record has to exist in db
        ///Post-condition: none
        ///Return        : true - if record was deleted successfully.
        ///				   false - if record was not deleted;
        /// </summary>
        public bool DeleteViolationNotice(int pViolationNoticeID)
        {
            string sqlDelete = " delete from ViolationNotice WHERE ID = @ID  ";
            SqlConnection conn = new SqlConnection(ConnectionString);
            bool pboolResult = false;

            // create the command for the stored procedure
            //SqlCommand sqlCmd = new SqlCommand("usDeleteViolationNotice");
            SqlCommand sqlCmd = new SqlCommand(sqlDelete);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            sqlCmd.Parameters.Add("@ID", SqlDbType.Int).Value = pViolationNoticeID;

            try
            {
                // open the connection and create the DataReader
                conn.Open();
                sqlCmd.ExecuteNonQuery();

                conn.Close();
                pboolResult = true;
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }
            return pboolResult;
        }
    }
}